package cn.sfa.service.agentinfo;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import javax.sql.rowset.serial.SerialException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.agenthonour.SaleAgentHonour;
import cn.sfa.entity.team.TeamAgentInfoEntity;
import cn.sfa.entity.users.SaleUserLoginLog;
import cn.sfa.service.meeting.MeetingService;


@Service
@Transactional
public class AgentInfoService {
	Logger logger = LoggerFactory.getLogger(MeetingService.class);
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	
	@Autowired
	private DefaultLobHandler lobHandler; 
	
	
	/**
	 * 获取代理人信息
	 * TODO
	 * @author: LiXiang
	 * @param agentId
	 * @return
	 */
	public SaleAgentHonour getAgentByAgentId(String agentId){
		String sql = "select * from saleagenthonour where agentid = :agentId";
		final SaleAgentHonour agentHonour=new SaleAgentHonour();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("agentId",agentId);
		jdbcTemplate.query(sql,paramMap ,new RowCallbackHandler() {
					
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						
						agentHonour.setSaleAgentHonourId(rs.getString("saleAgentHonourId"));
						agentHonour.setAffixMainId(rs.getString("affixMainId"));
						agentHonour.setAgentCode(rs.getString("agentCode"));
						agentHonour.setAgentId(rs.getString("agentId"));
						agentHonour.setAgentInfo(rs.getString("agentInfo"));
						agentHonour.setAgentName(rs.getString("agentName"));
						agentHonour.setBranchId(rs.getString("branchId"));
						agentHonour.setValidFlag(rs.getString("validFlag"));
						
						agentHonour.setHonourVaildDate(rs.getInt("honourVaildDate"));
						agentHonour.setHonourDate(rs.getTimestamp("honourDate"));
						
						String clobText = lobHandler.getClobAsString(rs,"honourFeeling");
						agentHonour.setHonourFeeling(clobText);
					}
						
		});
		
		
		return agentHonour;
	}
	
	/**
	 * 新增代理人信息
	 * TODO
	 * @author: LiXiang
	 * @param saleAgentHonour
	 */
	public void saveAgentInfo(final SaleAgentHonour saleAgentHonour){
		String sql="insert into saleagenthonour("+
		"saleAgentHonourId,CREATEDATE,CREATEUSERID,UPDATEDATE," +
		"UPDATEUSERID,BRANCHID,agentId,agentCode," +
		"agentName,affixMainId,honourVaildDate,honourDate," +
		"validFlag,agentInfo,honourFeeling)"+
		" values(" +
		"?,?,?,?," +
		"?,?,?,?," +
		"?,?,?,?," +
		"?,?,?)";
		
		jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						String id=majorKeyGenerate.keyGenerate(TableNameEnum.SALEAGENTHONOUR);
						ps.setString(1, id);
						ps.setTimestamp(2, new Timestamp(saleAgentHonour.getCreateDate().getTime()));
						ps.setString(3, CommonUtil.jugeAndFixNull(saleAgentHonour.getCreateUser()));
						ps.setTimestamp(4, new Timestamp(saleAgentHonour.getUpdateDate().getTime()));
						ps.setString(5, CommonUtil.jugeAndFixNull(saleAgentHonour.getUpdateUser()));
						ps.setString(6, CommonUtil.jugeAndFixNull(saleAgentHonour.getBranchId()));
						ps.setString(7, CommonUtil.jugeAndFixNull(saleAgentHonour.getAgentId()));
						ps.setString(8, CommonUtil.jugeAndFixNull(saleAgentHonour.getAgentCode()));
						ps.setString(9, CommonUtil.jugeAndFixNull(saleAgentHonour.getAgentName()));
						ps.setString(10, CommonUtil.jugeAndFixNull(saleAgentHonour.getAffixMainId()));
						ps.setInt(11, saleAgentHonour.getHonourVaildDate());
						if(null == saleAgentHonour.getHonourDate()){
							ps.setString(12, "");
						}else{
							ps.setTimestamp(12, new Timestamp(saleAgentHonour.getHonourDate().getTime()));
						}
						ps.setString(13, CommonUtil.jugeAndFixNull(saleAgentHonour.getValidFlag()));
						ps.setString(14, CommonUtil.jugeAndFixNull(saleAgentHonour.getAgentInfo()));
						
						lobCreator.setClobAsString(ps, 15, saleAgentHonour.getHonourFeeling()); // 设置详细的CLOB字段的内容
					}
				});
	}
	

	/**
	 * 编辑代理人信息
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */
	public boolean updateAgentInfo(final SaleAgentHonour saleAgentHonour)
	{	
		String sql="update saleAgentHonour set "+
		"UPDATEDATE=?,UPDATEUSERID=?,"+
		"affixMainId=?,honourVaildDate=?,validFlag=?,"+
		"agentInfo=?,honourFeeling=?"+
		" where saleAgentHonourId=?";
		
		int count=	jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						
						ps.setTimestamp(1, new Timestamp(saleAgentHonour.getUpdateDate().getTime()));
						ps.setString(2, CommonUtil.jugeAndFixNull(saleAgentHonour.getUpdateId()));
						
						ps.setString(3, CommonUtil.jugeAndFixNull(saleAgentHonour.getAffixMainId()));
						ps.setInt(4, saleAgentHonour.getHonourVaildDate());
						ps.setString(5, CommonUtil.jugeAndFixNull(saleAgentHonour.getValidFlag()));
						ps.setString(6, CommonUtil.jugeAndFixNull(saleAgentHonour.getAgentInfo()));
						
						lobCreator.setClobAsString(ps, 7, saleAgentHonour.getHonourFeeling());
						
						ps.setString(8,CommonUtil.jugeAndFixNull(saleAgentHonour.getSaleAgentHonourId()));
					}
				});
			
		
		if(count>0)
		{
			return true;
		}
		return false;
	}
	/**
	 * 根据代理人ID获取所有信息
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */
	public TeamAgentInfoEntity getAgentinfoByAgentId(String agentId){
		String sql = "select m.branchId,m.agentname,m.agentcode,m.branchid from agentbroker m where m.agentid = :agentId";
		final TeamAgentInfoEntity agent=new TeamAgentInfoEntity();
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("agentId",agentId);
		jdbcTemplate.query(sql,paramMap ,new RowCallbackHandler() {
					
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						
						agent.setBranchname(rs.getString("branchId"));
						agent.setAgentname(rs.getString("agentname"));
						agent.setAgentlevel(rs.getString("agentcode"));
						
					}
						
		});
		
		
		return agent;
	}
	
	/**
	 * 根据查询获取所有信息
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */	
	public Pagination seachPromot(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String currentBranchId, //机构id
			String agentName,	//代理人名字
			String agentCode,	//代理人编号
			String agentBranch, //代理人分支机构
			String beginCreateDate,
			String endCreateDate,
			String validFlag,
			int number
			){	
		Map<String, Object> paraMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder(); 
		sqlStr.append("select * from table(pkg_sfa_list.seachPromot(:agentName,:agentCode,:agentBranch,:beginCreateDate,:endCreateDate,:validFlag))");
		paraMap.put("agentName", agentName);
		paraMap.put("agentCode", agentCode);
		paraMap.put("agentBranch", agentBranch);
		paraMap.put("beginCreateDate", beginCreateDate);
		paraMap.put("endCreateDate", endCreateDate);
		paraMap.put("validFlag", validFlag);
		return new Pagination(sqlStr.toString(), paraMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}
	
	public Pagination seachPromotPre(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String currentBranchId //机构id
			){	
		Map<String, Object> paraMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder(); 
		sqlStr.append(" select * from saleagenthonour s where s.validflag = '1' and months_between(sysdate,s.honourdate)<=s.honourvailddate and rownum<=4 order by s.honourdate desc ");
		paraMap.put("currentBranchId", currentBranchId);	
		return new Pagination(sqlStr.toString(), paraMap, currentPage,
				numPerPage, url, jdbcTemplate);

	}
}
